package com.zhuiYun.project.api.handworkdiy.mapper;

import com.zhuiYun.project.api.handworkdiy.entity.CommodityAndDiyVo;
import com.zhuiYun.project.api.handworkdiy.entity.HandworkDiy;
import com.zhuiYun.project.api.handworkdiy.entity.HandworkDiyVo;
import com.zhuiYun.project.common.mapper.BaseMapper;
import org.apache.ibatis.annotations.*;

import java.util.List;
import java.util.Map;

@Mapper
public interface HandworkDiyMapper extends BaseMapper<HandworkDiy> {
    /**
     * @param //id 主图id 名称 原价 优惠价
     * @Author kdj
     * @Description 查询DIY的特价商品方法
     * @Date 2023/4/5 20:16
     * @Return java.util.List<com.zhuiYun.project.api.handworkdiy.entity.HandworkDiy>
     */
    @Select("SELECT * FROM handwork_diy where is_offer=1 and del_flag=0 order by diy_favorable_price asc limit 0,3")
    List<HandworkDiy> getHandworkDiyListByOffer();

    /**
     * @param
     * @Author kdj
     * @Description 查询DIY的特色商品方法
     * @Date 2023/4/5 21:02
     * @Return java.util.List<com.zhuiYun.project.api.handworkdiy.entity.HandworkDiy>
     */
    @Select("SELECT * FROM handwork_diy where is_characteristic=1 and del_flag=0 ")
    List<HandworkDiy> getHandworkDiyListByCharacteristic();

    /**
     * @param
     * @Author kdj
     * @Description 查询DIY的类型列表
     * @Date 2023/4/6 12:02
     * @Return java.util.List<map>
     */
    @Select("SELECT DISTINCT type.id As id, type.type_name AS typeName,type.image_id AS imageId " +
            "FROM handwork_diy AS diy " +
            "LEFT JOIN commodity_small_type AS small_type ON diy.diy_classification_id = small_type.id " +
            "LEFT JOIN commodity_type AS type ON small_type.type_id = type.id")
    List<Map> getHandworkDiyTypeList();

    /**
     * @param typeId
     * @param queryDiyName
     * @param minPrice
     * @param maxPrice
     * @param orderValue
     * @Author kdj
     * @Description 根据Pc页面条件查询Diy商品列表
     * @Date 2023/4/6 20:36
     * @Return java.util.List<com.zhuiYun.project.api.handworkdiy.entity.HandworkDiy>
     */
    @Select("<script> SELECT * FROM" +
            //最终价格
            "    (SELECT *, IFNULL(diy_favorable_price, diy_price) AS price" +
            "     FROM handwork_diy" +
            "    ) AS diy    " +
            "    LEFT JOIN commodity_small_type AS small_type " +
            "    ON diy.diy_classification_id = small_type.id " +
            "    <where> " +
            //根据价格筛选
            "  <choose>   <when test=\"minPrice != null and maxPrice != null \"> " +
            "    AND price BETWEEN LEAST( #{minPrice},  #{maxPrice}) AND GREATEST( #{minPrice},  #{maxPrice}) " +
            "    </when> " +
            "    <when  test=\"(minPrice == null or  minPrice == 0 ) and maxPrice != null and maxPrice != 0\"> " +
            "    AND price &lt; #{maxPrice} " +
            "    </when > " +
            "    <when  test=\"maxPrice == null and minPrice != null and minPrice != 0\"> " +
            "    AND price &gt; #{minPrice} " +
            "    </when ></choose>  " +
            //根据类型筛选
            "    <if test=\"typeId != null and typeId != 0\"> " +
            "    AND small_type.type_id = #{typeId} " +
            "    </if> " +
            //根据名称模糊查
            "    <if test=\"queryDiyName != null and queryDiyName != ''\"> " +
            "    AND diy.diy_name LIKE CONCAT('%', #{queryDiyName}, '%') " +
            "    </if> " +
            "  </where> " +
            //排序
            "<when test=\"orderValue != null and orderValue != ''\"> " +
            "   ORDER BY  ${orderValue} " +
            "</when> " +
            " </script>")
    List<Map> getHandworkDiyListByPageParams(Integer typeId, String queryDiyName, Integer minPrice, Integer maxPrice, String orderValue);

    @Select("<script>  " +
            "SELECT diy.*, IFNULL(diy.diy_favorable_price, diy.diy_price) AS price, " +
            "       CASE WHEN diy.diy_price = IFNULL(diy.diy_favorable_price, diy.diy_price) THEN 0 ELSE 1 END AS priceType " +
            "FROM handwork_diy diy " +
            "WHERE diy.id = #{id};" +
            "  </script>")
    HandworkDiyVo getHandworkDiyById(Integer id);

    /**
     * @param id
     * @Author kdj
     * @Description 计算diy商品评论总数量
     * @Date 2023/4/7 17:11
     * @Return int
     */
    @Select("<script>SELECT count(*) " +
            "FROM " +
            "handwork_diy INNER JOIN handwork_diy_order on handwork_diy.id=handwork_diy_order.diy_id " +
            "INNER JOIN orders_appraise ON handwork_diy_order.orders_id=orders_appraise.orders_id " +
            "where handwork_diy.id=#{id}</script>")
    int calculateTheTotalNumberOfComments(Integer id);

    /**
    * @Author kdj
    * @Description 根据diyid获得评论列表
    * @Date 2023/4/7 17:47
    * @Return java.util.List<java.util.Map>
    * @param id
    */
    @Select("<script>SELECT   " +
            " orders_content as content,orders_file as imgs,orders_video as video,user_id as userId,nick_name as userName,avatar_url as avUrl,orders_appraise.create_time as createTime   " +
            "FROM  orders_appraise     " +
            "LEFT JOIN handwork_diy_order on  handwork_diy_order.orders_id=orders_appraise.orders_id     " +
            "LEFT JOIN handwork_diy  ON handwork_diy.id=handwork_diy_order.diy_id  " +
            "LEFT JOIN  orders ON orders.id=handwork_diy_order.orders_id  " +
            "LEFT JOIN user ON user.id=orders.user_id    " +
            "where handwork_diy.id=#{id}</script>")
    List<Map<String,Object>> queryAllComments(Integer id);

    /**
    * @Description 减diy库存
    * @Author lzy
    * @Date 2023/4/10 10:53
    * @Return java.lang.Integer
    * @param id
    * @param number
    */
    @Update("update handwork_diy set diy_number = diy_number - #{number} where id = #{id}")
    Integer reduceInventory(@Param("id") Integer id,@Param("number") Integer number);

    /**
    * @Description 回滚库存
    * @Author lzy
    * @Date 2023/4/12 20:03
    * @Return java.lang.Integer
    * @param diyId
    * @param handworkDiyQuantity
    */
    @Update("update handwork_diy set diy_number = diy_number + #{number} where id = #{id}")
    Integer rollBackInventory(@Param("id") Integer diyId,@Param("number") Integer handworkDiyQuantity);

    @Select("SELECT id dId,diy_name FROM `handwork_diy` where diy_name like concat('%',#{selectValue},'%') ")
            @Results({
                    @Result(property = "dId",column = "dId")
            })
    // 查询diy商品的名称（uniapp搜索栏）
    List<CommodityAndDiyVo> getDiyCommodityName(String selectValue);

    /**
    * @Author kdj
    * @Description 计算diy商品评分
    * @Date 2023/4/13 19:18
    * @Return java.lang.Double
    * @param diyId
    */
    @Select("SELECT avg(orders_appraise.orders_star)  " +
            " FROM " +
            " handwork_diy INNER JOIN handwork_diy_order on handwork_diy.id=handwork_diy_order.diy_id  " +
            " INNER JOIN orders_appraise ON handwork_diy_order.orders_id=orders_appraise.orders_id  and  commodity_id=handwork_diy.id " +
            " where handwork_diy.id=#{diyId}")
    Double getDiyGrade(int diyId);
}
